package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author www.javaworkspace.com
 * 
 */
public class SQLiteDatabase {
	
	protected Connection connection = null;
	protected ResultSet resultSet = null;
	protected Statement statement = null;
	
	private String database = "jdbc:sqlite://home/joaocarlos/workspace/eclipse/PsycoManager/psyco.db";
	private String tableName;
	private int rowsPerPage;
	private String[] fieldList;
	private String[] primaryKey;
	private String query;

	/**
	 * JDBC connection stablish trouth SQLite database
	 */
	protected void connect()
	{
		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection(this.database);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Disconnect from JDBC SQLite connection.
	 */
	protected void disconnect()
	{
		try {
			resultSet.close();
			statement.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	protected void query()
	{
		try {
			this.statement = this.connection.createStatement();
			this.resultSet = statement.executeQuery(this.getQuery());			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				statement.close();
				connection.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}			
	}
	
	protected Object selectRecord(String where, Class sourceClass){
		/*query = "SELECT count(*) FROM "+tableName+where;
		query();*/ //Acho que não vamos precisar, pois era usado para paginação
		
		query = "SELECT * FROM " + tableName + where;
		query();
		Object object = null;
		try {
			object = sourceClass.newInstance();
			for(String item : fieldList)
				setItemValue(sourceClass, item, object, resultSet.getObject(item));
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return object;
	}
	
	protected boolean insertRecord(Class sourceClass){
		query = "INSERT INTO " + tableName + " SET ";
		String temp = "";
		for(String item : fieldList)
			temp += item + "='"+getItemValue(sourceClass,item)+"',";
		query += temp.substring(0, temp.length()-1);
		query();
		try {
			return resultSet.rowInserted();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	protected boolean updateRecord(Class sourceClass, String where){
		String update = "";
		String temp = "";
		for(String item : fieldList)
			temp += item + "='"+getItemValue(sourceClass,item)+"',";
		update += temp.substring(0, temp.length()-1);
		query = "UPDATE " + tableName + " SET " + update + " WHERE " + where;
		query();
		try {
			return resultSet.rowUpdated();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	private String getItemValue(Class sourceClass, String item){
		try {
			return sourceClass.getDeclaredField(item).get(this).toString();
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "";
	}
	
	private void setItemValue(Class sourceClass, String item, Object object, Object value){
		try {
			sourceClass.getDeclaredField(item).set(object, value);
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void setFieldList(String[] fieldList) {
		this.fieldList = fieldList;
	}
	
	public void setPrimaryKey(String[] primaryKey) {
		this.primaryKey = primaryKey;
	}
	
	public void setRowsPerPage(int rowsPerPage) {
		this.rowsPerPage = rowsPerPage;
	}
	
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}
	
	public void setQuery(String query) {
		this.query = query;
	}
	
	public String getDatabase() {
		return database;
	}
	
	public Connection getConnection() {
		return connection;
	}
	
	public String[] getFieldList() {
		return fieldList;
	}
	
	public String[] getPrimaryKey() {
		return primaryKey;
	}
	
	public String getQuery() {
		return query;
	}
	
	public ResultSet getResultSet() {
		return resultSet;
	}
	
	public int getRowsPerPage() {
		return rowsPerPage;
	}
	
	public Statement getStatement() {
		return statement;
	}
	
	public String getTableName() {
		return tableName;
	}
	
	public static void main(String[] args) {

		Connection connection = null;
		ResultSet resultSet = null;
		Statement statement = null;

		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager
					.getConnection("jdbc:sqlite://home/joaocarlos/workspace/eclipse/PsycoManager/psyco.db");
			statement = connection.createStatement();
			resultSet = statement
					.executeQuery("SELECT EMPNAME FROM EMPLOYEEDETAILS");
			while (resultSet.next()) {
				System.out.println("EMPLOYEE NAME:"
						+ resultSet.getString("EMPNAME"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				statement.close();
				connection.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}